
"""
数据库初始化脚本
用于创建测试数据库和表

如果连接失败，请根据您的 MySQL 安装修改以下配置：
- host: 数据库服务器地址，默认为 localhost
- port: 端口号，默认为 3306
- user: 用户名，默认为 root
- password: 密码，默认为123456，请根据您的安装设置
- database: 要创建的数据库名
"""

import pymysql

# 数据库配置，请根据您的 MySQL 安装进行修改
DB_CONFIG = {
    'host': 'localhost',  # 本地主机
    'port': 3306,        # MySQL 默认端口
    'user': 'root',      # MySQL 用户名
    'password': '123456',      # MySQL 密码（请根据您的安装设置）
    'database': 'test_db'  # 数据库名
}

print(f"尝试连接到 MySQL 服务器: {DB_CONFIG['host']}:{DB_CONFIG['port']}")

def check_mysql_server():
    """检查 MySQL 服务器是否运行"""
    try:
        import socket
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        result = sock.connect_ex(('localhost', 3306))
        sock.close()
        if result == 0:
            print("MySQL 服务器正在运行")
            return True
        else:
            print("警告: 无法连接到 MySQL 服务器，请确保 MySQL 已启动")
            return False
    except Exception as e:
        print(f"检查 MySQL 服务器状态时出错: {e}")
        return False

def create_database():
    """创建数据库"""
    try:
        # 检查 MySQL 服务器是否运行
        if not check_mysql_server():
            print("请启动 MySQL 服务器后再试")
            return False

        # 连接到 MySQL 服务器（不指定数据库）
        print(f"尝试连接到 MySQL 服务器: {DB_CONFIG['host']}:{DB_CONFIG['port']}")
        connection = pymysql.connect(
            host=DB_CONFIG['host'],
            port=DB_CONFIG['port'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )

        with connection.cursor() as cursor:
            # 创建数据库（如果不存在）
            sql = f"CREATE DATABASE IF NOT EXISTS `{DB_CONFIG['database']}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
            cursor.execute(sql)
            print(f"数据库 `{DB_CONFIG['database']}` 创建成功")

        connection.commit()
        connection.close()
        return True
    except Exception as e:
        print(f"创建数据库失败: {e}")
        return False

def create_tables():
    """创建表"""
    try:
        # 连接到数据库
        connection = pymysql.connect(
            host=DB_CONFIG['host'],
            port=DB_CONFIG['port'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            database=DB_CONFIG['database'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )

        with connection.cursor() as cursor:
            # 创建 users 表
            sql = """
            CREATE TABLE IF NOT EXISTS users (
                id INT PRIMARY KEY AUTO_INCREMENT,
                username VARCHAR(50) NOT NULL,
                email VARCHAR(100) NOT NULL,
                age INT,
                created_at DATETIME,
                comment VARCHAR(255)
            )
            """
            cursor.execute(sql)
            print("users 表创建成功")

            # 创建 departments 表
            sql = """
            CREATE TABLE IF NOT EXISTS departments (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(50) NOT NULL,
                manager VARCHAR(50),
                location VARCHAR(100),
                budget DECIMAL(10,2),
                established_date DATE
            )
            """
            cursor.execute(sql)
            print("departments 表创建成功")

            # 创建 products 表
            sql = """
            CREATE TABLE IF NOT EXISTS products (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(100) NOT NULL,
                description TEXT,
                price DECIMAL(10,2) NOT NULL,
                stock_quantity INT DEFAULT 0,
                created_at DATETIME,
                category VARCHAR(50)
            )
            """
            cursor.execute(sql)
            print("products 表创建成功")

            # 插入一些测试数据
            # 插入部门数据
            sql = "INSERT IGNORE INTO departments (name, manager, location, budget, established_date) VALUES (%s, %s, %s, %s, %s)"
            departments = [
                ("技术部", "张经理", "北京总部", 2000000.00, "2020-01-01"),
                ("市场部", "李经理", "上海分部", 1500000.00, "2019-06-15"),
                ("财务部", "王经理", "广州分部", 1000000.00, "2018-03-20"),
                ("人力资源部", "赵经理", "深圳分部", 800000.00, "2021-05-10")
            ]
            cursor.executemany(sql, departments)
            print("部门测试数据插入成功")

            # 插入产品数据
            sql = "INSERT IGNORE INTO products (name, description, price, stock_quantity, created_at, category) VALUES (%s, %s, %s, %s, %s, %s)"
            products = [
                ("智能手机", "高性能智能手机，配备先进的摄像头和处理器", 3999.99, 100, "2023-01-01", "电子产品"),
                ("笔记本电脑", "轻薄便携的笔记本电脑，适合办公和娱乐", 5999.99, 50, "2023-02-15", "电脑设备"),
                ("无线耳机", "高品质无线耳机，提供卓越的音质体验", 999.99, 200, "2023-03-10", "音频设备"),
                ("智能手表", "多功能智能手表，支持健康监测和运动追踪", 1999.99, 80, "2023-04-05", "可穿戴设备"),
                ("平板电脑", "大屏幕平板电脑，适合阅读和娱乐", 2999.99, 60, "2023-05-20", "电子产品")
            ]
            cursor.executemany(sql, products)
            print("产品测试数据插入成功")

        connection.commit()
        connection.close()
        return True
    except Exception as e:
        print(f"创建表失败: {e}")
        return False

if __name__ == "__main__":
    print("开始初始化数据库...")

    # 创建数据库
    if create_database():
        print("数据库创建成功")

        # 创建表
        if create_tables():
            print("表创建成功，测试数据已插入")
            print("数据库初始化完成！")
        else:
            print("表创建失败")
    else:
        print("数据库创建失败")
